// Here we add bank assets and other characteristics


cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data-bank-assets"

// input the link created in "data-bank-assets"
import excel using commerial_bank_link.xlsx, clear  firstrow
keep lupcoid entity legal_name
rename legal_name legal_name_link
drop if mi(entity)
bysort lupcoid entity: keep if _n==1
bysort entity: keep if _n==1
save entity-lupcoid, replace

// load bank data and connect
use bank_regulatory_commercial_balance_1995, clear

keep RSSD9001 RSSD9010 RSSD9999 RCFD2170 RCFD2200 RCFD2125 RCFD1600

label var RSSD9001 "Entity ID"
label var RSSD9010 "Entity Name"
label var RSSD9999 "Date"
label var RCFD2170  "TOTAL ASSETS"
label var RCFD2200 "TOTAL DEPOSITS"
label var RCFD2125 "TOTAL LOANS AND LEASES, NET"
label var RCFD1600 " COMMERCIAL AND INDUSTRIAL LOANS (TOTAL LOANS OUTSTANDING)"
compress

gen legal_name=lower(RSSD9010)
rename RSSD9001 entity
tostring RSSD9999, force replace
gen date=mofd(date(RSSD9999,"YMD"))
format %tm date
drop RSSD9999 RSSD9010 

rename RCFD2170 total_assets
rename RCFD2200 total_deposits
rename RCFD2125 total_loans
rename RCFD1600 commercial_loans

gsort entity date - total_assets
bysort entity date: keep if _n==1

joinby entity using entity-lupcoid, unm(both)
tab _m
keep if _m==3
drop _m

collapse (sum)  total_loans total_assets total_deposits commercial_loans, by(lupcoid date)

format date %tm

// get a three year growth rate for the assets
tsset lupcoid date, monthly
gen asset_growth=(total_assets-L36.total_assets)/L36.total_assets
winsor asset_growth, gen(asset_growth_3) p(0.01)
sum asset_growth_3, de


gen has_deposits=0
replace has_deposit=1 if (total_deposits>0) & !mi(total_deposits)
replace total_deposits=. if mi(total_deposits)
compress

label var asset_growth_3 "Asset Growth"

// convert assets to millions
replace total_assets=total_assets/1000
rename lupcoid ultimateparentid

bysort ultimateparentid date: keep if _n==1
cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"
save bank-assets, replace
